<!--- Default the execution (do the DB import) to false --->
<cfparam name="doImport" default="false" />
<!--- Include our UDF library for string parsing --->
<cfinclude template="lib.cfm" />

<div class="post">
	<h1 class="title"><a href="#">IGDB - Import</a></h1>
	<p class="byline"><small>Getting the games into the DB</small></p>
	<div class="entry">
		<p>
			This program will import all the games downloaded from the PDA-compatible archive of HTML
			files obtained from Improv Encyclopedia.  Many thanks to those kind folks for typing all
			this stuff in, even though it's not accessible as pure data.  This program will parse the
			HTML files into raw data.
		</p>
		<!---
		<h3>Make a collection of category files</h3>
		<p>
			Making the collection...
			<cfcollection action="create" collection="igdb_categories" path="#expandPath('.')#\source\categories" /> done.<br />
		</p>
		<h3>Peek into the category collection</h3>
		<p>
			<cfcollection action="list" name="igdb_categories" />
			<cfquery name="qoq" dbtype="query">
			    SELECT * from igdb_categories
			    WHERE igdb_categories.name = 'igdb_categories'
			</cfquery>
			<cfif qoq.recordcount GT 0>
			    <!--- Collection exists --->
			    There are <cfdump var = #qoq.size# /> items.
			<cfelse>
				Nothing found.
			</cfif>
		</p>
		--->
		<h3>Check what we have on disk</h3>
		<p>
			<cfoutput>
				Reading files <code>game_*.html</code> from <code>#expandPath('.')#\source\games</code> ...<br /><br />
				<cfset sGamePath = "#expandPath('.')#\source" />
				<cfdirectory action="list" listinfo="name" name="qGameFiles" directory="#sGamePath#" filter="game_*.html" sort="name asc" />
				<cfif (doImport)><cfset nEndRow = qGameFiles.recordCount /><cfelse><cfset nEndRow = 8 /></cfif>
				<cfloop query="qGameFiles" startrow="1" endrow="#nEndRow#">

					<!--- Get the name of the game by reformatting the game HTML file name --->
					<cfset sName = name />
					<cfset sName = REReplaceNoCase(sName, '(^game_|\.html)', '', 'all') />
					<cfset sName = ReplaceNoCase(sName, '_', ' ', 'all') />
					<!--- The description is tougher, it's inside the game HTML file --->
					<cffile action="read" file="#sGamePath#\#name#" variable="sGameFile" />
					<cfset stcontent = GetContainer(sGameFile, '<p class="iemain">', '</p>') />
					<cfset sDescription = ReplaceNoCase(stripHTML(stcontent.contents.str), "How it Works", "") />
					<!--- Use a Verity collection of category HTML files to find out which categories the game is in --->
					<cfset sCriteria = replaceNoCase(sName, "'", "", "all") />
					<cfsearch name="qCatSearch" collection="igdb_categories" type="explicit" criteria="'#sCriteria#'" />
					<cfset sCatList = "" />
					<cfloop query="qCatSearch">
						<cfset sCat = listLast(key, '\') />
						<cfset sCat = REReplaceNoCase(sCat, '(^category_|\.html)', '', 'all') />
						<cfset sCat = ReplaceNoCase(sCat, '_', ' ', 'all') />
						<cfif (currentRow neq 1)><cfset sCatList = sCatList & ", " /></cfif>
						<cfset sCatList = sCatList & sCat />
					</cfloop>

					<!--- Do the import --->
					<cfif (doImport)>
						<!--- See if the game is already in there --->
						<cfquery name="qGameCheck" datasource="sixdegreesimprov">
							SELECT game_id, game_name FROM igdb_games
							WHERE game_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(sName)#" />
						</cfquery>
						<cfset gameId = qGameCheck.game_id />
						<cfif (not qGameCheck.recordCount)>
							<cfquery name="qAddGame" datasource="sixdegreesimprov" result="stAddGame">
								INSERT INTO igdb_games (game_name, descrip)
								VALUES(
									<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(sName)#" />,
									<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(sDescription)#" />
								)
							</cfquery>
							<cfset gameId = stAddGame.generated_key />
						</cfif>
						<!--- Time to loop over categories --->
						<cfloop list="#sCatList#" index="i">
							<cfset sCurCat = Trim(i) />
							<!--- Check for the cat in the DB --->
							<cfquery name="qCatCheck" datasource="sixdegreesimprov">
								SELECT category_id, category_name FROM igdb_categories
								WHERE category_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(sCurCat)#" />
							</cfquery>
							<cfset categoryId = qCatCheck.category_id />
							<!--- If it's not there, put it in --->
							<cfif (not qCatCheck.recordCount)>
								<cfquery name="qAddCat" datasource="sixdegreesimprov" result="stAddCat">
									INSERT INTO igdb_categories (category_name)
									VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(i)#" />)
								</cfquery>
								<cfset categoryId = stAddCat.generated_key />
							</cfif>
							<!--- Create the game to category link --->
							<cfquery name="qAddLink" datasource="sixdegreesimprov">
								INSERT INTO igdb_game_category (game_id, category_id)
								VALUES (
									<cfqueryparam cfsqltype="cf_sql_integer" value="#gameId#" />,
									<cfqueryparam cfsqltype="cf_sql_integer" value="#categoryId#" />
								)
							</cfquery>
						</cfloop>
					</cfif>

					<!--- Finally, render out the game and it's attributes --->
					<strong>Game:</strong> #sName#<br />
					<cfif (doImport)>
						<strong>Imported: ID=#gameId#</strong><br />
					</cfif>
					<strong>Description:</strong> #sDescription#<br />
					<strong>Categories:</strong> #sCatList#<br />
					<br /><br />
					<cfflush />
				</cfloop>
			</cfoutput>
		</p>
	</div>
</div>